using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using DTO;
namespace DAO
{
   public  class ThanhLySachDAO
    {
       public static DataTable laydsthanhlysach_DAO()
       {

           string sql = "select tls.MaThanhLySach,s.MaSach, s.TenSach, cttl.LyDo,ld.TenLyDo,tls.NgayThanhLy,tls.NguoiThanhLy,nv.HoTen ";
           sql+= " from LyDoThanhLy as ld,ThanhLySach as tls, ChiTietThanhLy as cttl,Sach s,NhanVien as nv where ld.MaThanhLy=cttl.LyDo and cttl.MaThanhLy=tls.MaThanhLySach and s.MaSach=cttl.MaSach and tls.NguoiThanhLy=nv.MaNhanVien";
           DataTable dt = DataProvider.ExcuteQuery(sql);
           return dt;

       }

       public static void ThemPhieuThanhLy_DAO(ThanhLySachDTO tlsDTO)
       {
           string sql = "Insert into ThanhLySach(NguoiThanhLy,NgayThanhLy) values(" + tlsDTO.Nguoithanhly.ToString() + ",'" + tlsDTO.Ngaythanhly.ToString() + "')";
           DataProvider.ExecutenonQuery(sql);
           sql = "Insert into ChiTietThanhLy(MaThanhLy,MaSach,LyDo) values("+tlsDTO.ChiTiet.Mathanhly.ToString()+","+tlsDTO.ChiTiet.Masach.ToString()+","+tlsDTO.ChiTiet.Lydo.ToString()+")";
           DataProvider.ExecutenonQuery(sql);
       }

       public static int LayThanhLySachCuoiCung()
       {
           string sql = "select MaThanhLySach from ThanhLySach order by MaThanhLySach DESC";
           DataTable dt = DataProvider.ExcuteQuery(sql);
           int id =-1;
           id= int.Parse(dt.Rows[0][0].ToString());
           return id;
       }

       public static void XoaThanhLySach(int maThanhLy)
       {
           string sql = "delete from ThanhLySach where MaThanhLySach=" + maThanhLy.ToString();
           DataProvider.ExecutenonQuery(sql);
           sql = "Delete from ChiTietThanhLy where MaThanhLy=" + maThanhLy.ToString();
           DataProvider.ExecutenonQuery(sql);
       }

       public static void CapNhatThanhLySach_DAO(ThanhLySachDTO tlsDTO)
       {
           string sql = "update ThanhLySach set NguoiThanhLy=" + tlsDTO.Nguoithanhly.ToString() + ",NgayThanhLy='" + tlsDTO.Ngaythanhly.ToString() + "' where MaThanhLySach=" + tlsDTO.Mathanhlysach.ToString();
           DataProvider.ExecutenonQuery(sql);
           sql = "update ChiTietThanhLy set MaSach=" + tlsDTO.ChiTiet.Masach + ",LyDo=" + tlsDTO.ChiTiet.Lydo.ToString() + " where MaThanhLy=" + tlsDTO.Mathanhlysach.ToString();
           DataProvider.ExecutenonQuery(sql);
       }

       public static DataTable TimThanhLySach_DAO(int maSach)
       {
           string sql = "select tls.MaThanhLySach,s.MaSach, s.TenSach, cttl.LyDo,ld.TenLyDo,tls.NgayThanhLy,tls.NguoiThanhLy,nv.HoTen ";
           sql += " from LyDoThanhLy as ld,ThanhLySach as tls, ChiTietThanhLy as cttl,Sach s,NhanVien as nv where ld.MaThanhLy=cttl.LyDo and cttl.MaThanhLy=tls.MaThanhLySach and s.MaSach=cttl.MaSach and tls.NguoiThanhLy=nv.MaNhanVien and s.MaSach="+maSach.ToString();
           DataTable dt = DataProvider.ExcuteQuery(sql);
           return dt;
       }
    }
}
